/**
	AUTHOR		: VEERU
	DATE		: FEB 28th 2004
	DESCRIPTION	: THIS PROCEDURE IS USED TO REMOVE THE DUPLICATE VALUES IN FCMASTER TABLE.
	USAGE		: EXEC SP_REMOVE_DUPLICATE_FORECAST_ENTRY
*/

CREATE  PROC SP_REMOVE_DUPLICATE_FORECAST_ENTRY
AS
BEGIN
	DECLARE @DUPLICATES_CURSOR CURSOR
	DECLARE @PROJID INT
	DECLARE @PRODID INT
	DECLARE @MONTH INT
	DECLARE @YEAR INT	

	DECLARE @SPERSONID INT
	DECLARE @CUSTID    INT
	DECLARE @ODMID     INT
	DECLARE @FCCURSOR  CURSOR	

	SET @DUPLICATES_CURSOR = CURSOR FOR SELECT PROJID, PRODID, MONTH, YEAR from FCENTRYMASTER GROUP BY PROJID, PRODID, YEAR, MONTH HAVING COUNT(*) > 1
	OPEN @DUPLICATES_CURSOR
	FETCH NEXT FROM @DUPLICATES_CURSOR INTO @PROJID, @PRODID, @MONTH, @YEAR
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		INSERT INTO FCENTRYHISTORY ( ROWID, SPERSONID, PRODID, CUSTID, ODMID, PROJID, MONTH, QUARTER, YEAR, QTY, PRICE,
					     CONFIDENCE_LEVEL, USERLIST1, USERDATE1, USERTXT1,USERTXT2, USERID, ENTRYDATE )
					   ( SELECT ROWID, SPERSONID, PRODID, CUSTID, ODMID,PROJID,MONTH, QUARTER, YEAR, QTY, PRICE,
					     CONFIDENCE_LEVEL, USERLIST1, USERDATE1,USERTXT1,USERTXT2, USERID, ENTRYDATE 
					FROM FCENTRYMASTER WHERE PROJID = @PROJID AND PRODID = @PRODID AND MONTH = @MONTH AND YEAR = @YEAR AND EntryDate != ( SELECT MAX(EntryDate) FROM FCENTRYMASTER WHERE PROJID = @PROJID AND PRODID = @PRODID AND MONTH = @MONTH AND YEAR = @YEAR ) )		
		
		DELETE FROM FCENTRYMASTER WHERE PROJID = @PROJID AND PRODID = @PRODID AND MONTH = @MONTH AND YEAR = @YEAR AND EntryDate != ( SELECT MAX(EntryDate) FROM FCENTRYMASTER WHERE PROJID = @PROJID AND PRODID = @PRODID AND MONTH = @MONTH AND YEAR = @YEAR )
		
		SET @PROJID = NULL
		SET @PRODID = NULL
		SET @MONTH = NULL
		SET @YEAR = NULL
		FETCH NEXT FROM @DUPLICATES_CURSOR INTO @PROJID, @PRODID, @MONTH, @YEAR
	 END
	CLOSE @DUPLICATES_CURSOR
	DEALLOCATE @DUPLICATES_CURSOR
	
	
	SET @FCCURSOR = CURSOR FOR SELECT DISTINCT FC.PROJID FROM FCENTRYMASTER FC, PROJSETUP PS WHERE FC.PROJID = PS.PROJID
	AND ( FC.SPERSONID != PS.SPERSONID OR ISNULL(FC.CUSTID, 0 ) != PS.CUSTID OR FC.ODMID != ISNULL( PS.ODMID, PS.CUSTID ))
	OPEN @FCCURSOR
	FETCH NEXT FROM @FCCURSOR INTO @PROJID 
	WHILE @@FETCH_STATUS = 0
	BEGIN	
		SELECT  @SPERSONID = SPERSONID, @CUSTID = CUSTID, @ODMID = ODMID FROM PROJSETUP WHERE PROJID = @PROJID
		INSERT INTO FCENTRYHISTORY ( ROWID, SPERSONID, PRODID, CUSTID, ODMID, PROJID, MONTH, QUARTER, YEAR, QTY, PRICE,
					     CONFIDENCE_LEVEL, USERLIST1, USERDATE1, USERTXT1,USERTXT2, USERID, ENTRYDATE )
					   ( SELECT ROWID, SPERSONID, PRODID, CUSTID, ODMID,PROJID,MONTH, QUARTER, YEAR, QTY, PRICE,
					     CONFIDENCE_LEVEL, USERLIST1, USERDATE1,USERTXT1,USERTXT2, USERID, ENTRYDATE 
					FROM FCENTRYMASTER WHERE PROJID = @PROJID )
		--UPDATING SALES FORCAST DETAILS WITH THE NEW PROJECT DETAILS
		UPDATE FCENTRYMASTER SET SPERSONID = @SPERSONID, CUSTID = @CUSTID, ODMID = ISNULL( @ODMID, @CUSTID ) WHERE PROJID = @PROJID

		SET @PROJID = NULL
		FETCH NEXT FROM @FCCURSOR INTO @PROJID 
	END
	CLOSE @FCCURSOR
	DEALLOCATE @FCCURSOR
END


